﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class CycleCountRequestDA : ICycleCountRequestDA
    {
        private string AREA_ID_END = "@AREA_ID_END";
        private string AREA_ID_START = "@AREA_ID_START";
        private string CREATED_BY = "@CREATED_BY";
        private string LOC_END = "@LOC_END";
        private string LOC_LEVEL_END = "@LOC_LEVEL_END";
        private string LOC_LEVEL_START = "@LOC_LEVEL_START";
        private string LOC_START = "@LOC_START";
        private string OWNER_ID_END = "@OWNER_ID_END";
        private string OWNER_ID_START = "@OWNER_ID_START";
        private string RELEASE_ID = "@RELEASE_ID";
        private string REQUEST_ID = "@REQUEST_ID";
        private string SKU_ID_END = "@SKU_ID_END";
        private string SKU_ID_START = "@SKU_ID_START";
        private string SQL_CHECK_CYCLE_COUNT_REQUEST_ID_UNIQUE = " SELECT COUNT(1) FROM @_@CYCLE_COUNT_REQUEST WHERE REQUEST_ID = @REQUEST_ID ";
        private string SQL_DELETE_CYCLE_COUNT_REQUEST = " DELETE FROM @_@CYCLE_COUNT_REQUEST WHERE REQUEST_ID = @REQUEST_ID ";
        private string SQL_INSERT_CYCLE_COUNT_REQUEST = " INSERT INTO @_@CYCLE_COUNT_REQUEST ( WH_ID, REQUEST_ID, STATUS, OWNER_ID_START, OWNER_ID_END, SKU_ID_START, SKU_ID_END, LOC_START, LOC_END, LOC_LEVEL_START, LOC_LEVEL_END, ZONE_ID_START, ZONE_ID_END, AREA_ID_START, AREA_ID_END, RELEASE_ID, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE) VALUES ( @WH_ID, @REQUEST_ID, @STATUS, @OWNER_ID_START, @OWNER_ID_END, @SKU_ID_START, @SKU_ID_END, @LOC_START, @LOC_END, @LOC_LEVEL_START, @LOC_LEVEL_END, @ZONE_ID_START, @ZONE_ID_END, @AREA_ID_START, @AREA_ID_END, @RELEASE_ID, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE() )  ";
        private string SQL_SELECT_ALL_CYCLE_COUNT_REQUEST = " SELECT  ROWID, WH_ID, REQUEST_ID, STATUS, OWNER_ID_START, OWNER_ID_END, SKU_ID_START, SKU_ID_END, LOC_START, LOC_END, LOC_LEVEL_START, LOC_LEVEL_END, ZONE_ID_START, ZONE_ID_END, AREA_ID_START, AREA_ID_END, RELEASE_ID, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE  FROM  @_@CYCLE_COUNT_REQUEST ";
        private string SQL_SELECT_ALL_CYCLE_COUNT_REQUEST_LANGUAGE = " SELECT  CCR.ROWID, CCR.WH_ID, CCR.REQUEST_ID, CCR.STATUS, CCR.OWNER_ID_START, CCR.OWNER_ID_END, CCR.SKU_ID_START, CCR.SKU_ID_END, CCR.LOC_START, CCR.LOC_END, CCR.LOC_LEVEL_START, CCR.LOC_LEVEL_END, CCR.ZONE_ID_START, CCR.ZONE_ID_END, CCR.AREA_ID_START, CCR.AREA_ID_END, CCR.RELEASE_ID, CCR.CREATED_BY, CCR.CREATED_DATE, CCR.UPDATED_BY, CCR.UPDATED_DATE,T.[TEXT] AS STATUS_D FROM  @_@CYCLE_COUNT_REQUEST CCR LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = CCR.STATUS AND SCD.SYS_CODE_TYPE = 'CCRSTATUS' LEFT JOIN sysadm.TRANSLATION T ON 'CCRSTATUS.'+CCR.STATUS = T.ITEM_ID  AND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY =@LANGUAGE ";
        private string SQL_SELECT_INV_LOT_LOC_LPN_BY__REQUEST = "SELECT INV.* FROM  @_@INV_LOT_LOC_LPN INV INNER JOIN @_@LOCATION L ON INV.LOC= L.LOC  LEFT JOIN @_@AREA_DETAIL A ON L.ZONE_ID=A.ZONE_ID INNER JOIN SYSADM.USER_OWNER UO ON   UO.OWNER_ID=INV.OWNER_ID AND UO.USER_ID='@USER_ID' ";
        private string SQL_SELECT_REQUEST_BY_CRITERIA = "SELECT TOP @TOP  ROWID, WH_ID, REQUEST_ID, STATUS, OWNER_ID_START, OWNER_ID_END, SKU_ID_START, SKU_ID_END, LOC_START, LOC_END, LOC_LEVEL_START, LOC_LEVEL_END, ZONE_ID_START, ZONE_ID_END, AREA_ID_START, AREA_ID_END, RELEASE_ID, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE  FROM  @_@CYCLE_COUNT_REQUEST WHERE  STATUS = 'NEW' ORDER BY  REQUEST_ID ASC";
        private string SQL_UPDATE_CYCLE_COUNT_REQUEST = " UPDATE @_@CYCLE_COUNT_REQUEST SET WH_ID = @WH_ID, STATUS = @STATUS, OWNER_ID_START = @OWNER_ID_START, OWNER_ID_END = @OWNER_ID_END, SKU_ID_START = @SKU_ID_START, SKU_ID_END = @SKU_ID_END, LOC_START = @LOC_START, LOC_END = @LOC_END, LOC_LEVEL_START = @LOC_LEVEL_START, LOC_LEVEL_END = @LOC_LEVEL_END, ZONE_ID_START = @ZONE_ID_START, ZONE_ID_END = @ZONE_ID_END, AREA_ID_START = @AREA_ID_START, AREA_ID_END = @AREA_ID_END, RELEASE_ID = @RELEASE_ID, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE REQUEST_ID = @REQUEST_ID  ";
        private string SQL_UPDATE_CYCLE_COUNT_REQUEST_STATUS = " UPDATE @_@CYCLE_COUNT_REQUEST SET STATUS = @STATUS  WHERE REQUEST_ID = @REQUEST_ID";
        private string STATUS = "@STATUS";
        private string UPDATED_BY = "@UPDATED_BY";
        private string WH_ID = "@WH_ID";
        private string ZONE_ID_END = "@ZONE_ID_END";
        private string ZONE_ID_START = "@ZONE_ID_START";

        public bool CheckCycleCountRequestIDUnique(string cycleCountRequestID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@REQUEST_ID", cycleCountRequestID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_CYCLE_COUNT_REQUEST_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckCycleCountRequestIDUnique(DataBase dataBase, DbTransaction tran, string cycleCountRequestID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@REQUEST_ID", cycleCountRequestID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_CYCLE_COUNT_REQUEST_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int DeleteCycleCountRequest(List<string> cycleCountRequestID, string whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int j = 0; j < cycleCountRequestID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@REQUEST_ID", cycleCountRequestID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_CYCLE_COUNT_REQUEST.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteCycleCountRequest(DataBase dataBase, DbTransaction tran, List<string> cycleCountRequestID, string whLoginID)
        {
            int result = 0;
            try
            {
                for (int j = 0; j < cycleCountRequestID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@REQUEST_ID", cycleCountRequestID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_CYCLE_COUNT_REQUEST.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetAllCycleCountRequest(string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_CYCLE_COUNT_REQUEST.Replace("@_@", whLoginID);
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetAllCycleCountRequest(DataBase dataBase, DbTransaction tran, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_CYCLE_COUNT_REQUEST.Replace("@_@", whLoginID);
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql);
        }

        public List<CycleCountRequestInfo> GetCycleCountRequestByCycleCountCriteria(DataBase dataBase, DbTransaction tran, CycleCountCriteriaInfo cycleCountCriteriaInfo, string whLoginID)
        {
            string strSql = this.SQL_SELECT_REQUEST_BY_CRITERIA.Replace("@_@", whLoginID).Replace("@TOP", cycleCountCriteriaInfo.MaxCounts.ToString());
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, strSql);
            List<CycleCountRequestInfo> cycleCountRequestList = new List<CycleCountRequestInfo>();
            while (reader.Read())
            {
                DateTime CS00002;
                CycleCountRequestInfo cycleCountRequestInfo = new CycleCountRequestInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["REQUEST_ID"].ToString(), reader["STATUS"].ToString(), reader["OWNER_ID_START"].ToString(), reader["OWNER_ID_END"].ToString(), reader["SKU_ID_START"].ToString(), reader["SKU_ID_END"].ToString(), reader["LOC_START"].ToString(), reader["LOC_END"].ToString(), (reader["LOC_LEVEL_START"].ToString() != "") ? int.Parse(reader["LOC_LEVEL_START"].ToString()) : 0, (reader["LOC_LEVEL_END"].ToString() != "") ? int.Parse(reader["LOC_LEVEL_END"].ToString()) : 0, reader["ZONE_ID_START"].ToString(), reader["ZONE_ID_END"].ToString(), reader["AREA_ID_START"].ToString(), reader["AREA_ID_END"].ToString(), reader["RELEASE_ID"].ToString(), reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()));
                cycleCountRequestList.Add(cycleCountRequestInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return cycleCountRequestList;
        }

        public CycleCountRequestInfo GetCycleCountRequestByID(string cyclecountrequestID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_CYCLE_COUNT_REQUEST.Replace("@_@", whLoginID) + " WHERE REQUEST_ID = @REQUEST_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@REQUEST_ID", cyclecountrequestID) };
            CycleCountRequestInfo cycleCountRequestInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    cycleCountRequestInfo = new CycleCountRequestInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["REQUEST_ID"].ToString(), reader["STATUS"].ToString(), reader["OWNER_ID_START"].ToString(), reader["OWNER_ID_END"].ToString(), reader["SKU_ID_START"].ToString(), reader["SKU_ID_END"].ToString(), reader["LOC_START"].ToString(), reader["LOC_END"].ToString(), (reader["LOC_LEVEL_START"].ToString() != "") ? int.Parse(reader["LOC_LEVEL_START"].ToString()) : 0, (reader["LOC_LEVEL_END"].ToString() != "") ? int.Parse(reader["LOC_LEVEL_END"].ToString()) : 0, reader["ZONE_ID_START"].ToString(), reader["ZONE_ID_END"].ToString(), reader["AREA_ID_START"].ToString(), reader["AREA_ID_END"].ToString(), reader["RELEASE_ID"].ToString(), reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
                }
            }
            return cycleCountRequestInfo;
        }

        public CycleCountRequestInfo GetCycleCountRequestByID(DataBase dataBase, DbTransaction tran, string cyclecountrequestID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_CYCLE_COUNT_REQUEST.Replace("@_@", whLoginID) + " WHERE REQUEST_ID = @REQUEST_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@REQUEST_ID", cyclecountrequestID) };
            CycleCountRequestInfo cycleCountRequestInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                cycleCountRequestInfo = new CycleCountRequestInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["REQUEST_ID"].ToString(), reader["STATUS"].ToString(), reader["OWNER_ID_START"].ToString(), reader["OWNER_ID_END"].ToString(), reader["SKU_ID_START"].ToString(), reader["SKU_ID_END"].ToString(), reader["LOC_START"].ToString(), reader["LOC_END"].ToString(), (reader["LOC_LEVEL_START"].ToString() != "") ? int.Parse(reader["LOC_LEVEL_START"].ToString()) : 0, (reader["LOC_LEVEL_END"].ToString() != "") ? int.Parse(reader["LOC_LEVEL_END"].ToString()) : 0, reader["ZONE_ID_START"].ToString(), reader["ZONE_ID_END"].ToString(), reader["AREA_ID_START"].ToString(), reader["AREA_ID_END"].ToString(), reader["RELEASE_ID"].ToString(), reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return cycleCountRequestInfo;
        }

        public DataSet GetCycleCountRequestByQueryList(List<string> sqlWhere, CycleCountRequestQueryEntity cyclecountrequestQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_CYCLE_COUNT_REQUEST_LANGUAGE.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage) };
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!cyclecountrequestQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, cyclecountrequestQuery.CurrentPage, cyclecountrequestQuery.PageSize, cyclecountrequestQuery.SortField, cyclecountrequestQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'");
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetCycleCountRequestByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, CycleCountRequestQueryEntity cyclecountrequestQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_CYCLE_COUNT_REQUEST.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage) };
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!cyclecountrequestQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, cyclecountrequestQuery.CurrentPage, cyclecountrequestQuery.PageSize, cyclecountrequestQuery.SortField, cyclecountrequestQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'");
            }
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public int InsertCycleCountRequest(CycleCountRequestInfo cycleCountRequestInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                cycleCountRequestInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_CycleCountRequest_Parameters(cycleCountRequestInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_CYCLE_COUNT_REQUEST.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int InsertCycleCountRequest(DataBase dataBase, DbTransaction tran, CycleCountRequestInfo cycleCountRequestInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                cycleCountRequestInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_CycleCountRequest_Parameters(cycleCountRequestInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_CYCLE_COUNT_REQUEST.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public List<CycleCountInventoryInfo> SelectInventoryByCycleRequest(DataBase dataBase, DbTransaction tran, List<string> SqlWhere, string UserID, string whLoginID)
        {
            string strSql = this.SQL_SELECT_INV_LOT_LOC_LPN_BY__REQUEST.Replace("@_@", whLoginID).Replace("@USER_ID", UserID);
            for (int i = 0; i < SqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    strSql = strSql + " WHERE " + SqlWhere[i].ToString();
                }
                else
                {
                    strSql = strSql + " AND " + SqlWhere[i].ToString();
                }
            }
            List<CycleCountInventoryInfo> CycleCountInventoryList = new List<CycleCountInventoryInfo>();
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, strSql);
            while (reader.Read())
            {
                DateTime CS00002;
                CycleCountInventoryInfo cycleCountInventoryInfo = new CycleCountInventoryInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["LOT"].ToString(), reader["LOC"].ToString(), reader["LPN_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["ALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["ALLOCATED_QTY"].ToString()) : 0M, (reader["PICKED_QTY"].ToString() != "") ? decimal.Parse(reader["PICKED_QTY"].ToString()) : 0M, (reader["EXPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["EXPECTED_QTY"].ToString()) : 0M, (reader["PICKINPROCESS_QTY"].ToString() != "") ? decimal.Parse(reader["PICKINPROCESS_QTY"].ToString()) : 0M, (reader["PENDING_MOVE_IN"].ToString() != "") ? decimal.Parse(reader["PENDING_MOVE_IN"].ToString()) : 0M, (reader["ARCHIVE_QTY"].ToString() != "") ? decimal.Parse(reader["ARCHIVE_QTY"].ToString()) : 0M, (reader["ARCHIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["ARCHIVE_DATE"].ToString()) : (CS00002 = new DateTime()), reader["STATUS"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()));
                CycleCountInventoryList.Add(cycleCountInventoryInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return CycleCountInventoryList;
        }

        private SqlParameter[] Set_CycleCountRequest_Parameters(CycleCountRequestInfo cycleCountRequestInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.REQUEST_ID, SqlDbType.VarChar, 20), new SqlParameter(this.STATUS, SqlDbType.VarChar, 20), new SqlParameter(this.OWNER_ID_START, SqlDbType.VarChar, 20), new SqlParameter(this.OWNER_ID_END, SqlDbType.VarChar, 20), new SqlParameter(this.SKU_ID_START, SqlDbType.VarChar, 50), new SqlParameter(this.SKU_ID_END, SqlDbType.VarChar, 50), new SqlParameter(this.LOC_START, SqlDbType.VarChar, 20), new SqlParameter(this.LOC_END, SqlDbType.VarChar, 20), new SqlParameter(this.LOC_LEVEL_START, SqlDbType.Int, 4), new SqlParameter(this.LOC_LEVEL_END, SqlDbType.Int, 4), new SqlParameter(this.ZONE_ID_START, SqlDbType.VarChar, 20), new SqlParameter(this.ZONE_ID_END, SqlDbType.VarChar, 20), new SqlParameter(this.AREA_ID_START, SqlDbType.VarChar, 20), new SqlParameter(this.AREA_ID_END, SqlDbType.VarChar, 20), new SqlParameter(this.RELEASE_ID, SqlDbType.VarChar, 20), 
                new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20)
             };
            if (!string.IsNullOrEmpty(cycleCountRequestInfo.WhID))
            {
                paramArray[0].Value = cycleCountRequestInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = cycleCountRequestInfo.RequestID;
            paramArray[2].Value = cycleCountRequestInfo.Status;
            paramArray[3].Value = cycleCountRequestInfo.OwnerIdStart;
            paramArray[4].Value = cycleCountRequestInfo.OwnerIdEnd;
            paramArray[5].Value = cycleCountRequestInfo.SkuIdStart;
            paramArray[6].Value = cycleCountRequestInfo.SkuIdEnd;
            paramArray[7].Value = cycleCountRequestInfo.LocStart;
            paramArray[8].Value = cycleCountRequestInfo.LocEnd;
            paramArray[9].Value = cycleCountRequestInfo.LocLevelStart;
            paramArray[10].Value = cycleCountRequestInfo.LocLevelEnd;
            paramArray[11].Value = cycleCountRequestInfo.ZoneIdStart;
            paramArray[12].Value = cycleCountRequestInfo.ZoneIdEnd;
            paramArray[13].Value = cycleCountRequestInfo.AreaIdStart;
            paramArray[14].Value = cycleCountRequestInfo.AreaIdEnd;
            if (!string.IsNullOrEmpty(cycleCountRequestInfo.ReleaseID))
            {
                paramArray[15].Value = cycleCountRequestInfo.ReleaseID;
            }
            else
            {
                paramArray[15].Value = DBNull.Value;
            }
            paramArray[0x10].Value = cycleCountRequestInfo.CreatedBy;
            paramArray[0x11].Value = cycleCountRequestInfo.UpdatedBy;
            return paramArray;
        }

        public int UpdateCycleCountRequest(CycleCountRequestInfo cycleCountRequestInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                cycleCountRequestInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_CycleCountRequest_Parameters(cycleCountRequestInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_CYCLE_COUNT_REQUEST.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdateCycleCountRequest(DataBase dataBase, DbTransaction tran, CycleCountRequestInfo cycleCountRequestInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                cycleCountRequestInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_CycleCountRequest_Parameters(cycleCountRequestInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_CYCLE_COUNT_REQUEST.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateCycleCountRequestStatus(DataBase dataBase, DbTransaction tran, string Status, string RequestID, string whLoginID)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@STATUS", Status), new SqlParameter("@REQUEST_ID", RequestID) };
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_CYCLE_COUNT_REQUEST_STATUS.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

